﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace IBSGate
{
    /// <summary>
    /// 与Excel文件操纵
    /// </summary>
    public class IBSExcelOperator
    {

        /// <summary>
        /// 从Excel中读出DataTable
        /// 默认sheet为第一个
        /// </summary>
        /// <param name="path">路径及文件名</param>
        /// <returns></returns>
        public DataTable GetDataTableFromExcel(string path)
        {
            return GetDataTableFromExcel(path, "1");
        }

        /// <summary>
        /// 从Excel中读出DataTable
        /// </summary>
        /// <param name="path">路径及文件名</param>
        /// <param name="sheetName">Excel中的sheet名</param>
        /// <returns></returns>
        public DataTable GetDataTableFromExcel(string path, string sheetName)
        {
            DataTable result = new DataTable();

            string conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1;'";

            //Sheet1为excel中表的名字
            string sql = "select * from [" + sheetName + "$]";

            DataSet ds = new DataSet();


            try
            {
                OleDbCommand cmd = new OleDbCommand(sql, new OleDbConnection(conn));
                OleDbDataAdapter ad = new OleDbDataAdapter(cmd);

                ad.Fill(ds);

            }
            catch (OleDbException oleDbException)
            {
                throw new Exception("Excel表中的Sheet名不正确! 请检查!");
            }
            
            int count = ds.Tables[0].Rows.Count;

            result = ds.Tables[0];

            return result;
        }

        public bool SaveDataTableToExcel(DataTable timeTable, string path)
        {
            throw new NotImplementedException();
        }
    }
}
